package com.tool.o2m.model;


import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import java.util.List;

public class TableColumn {

    public String tableName;

    public Integer columnId;

    public String columnName;

    public String dataType;

    public Integer dataLength;

    public Integer charLength;

    public Integer dataPrecision;

    public Integer dataScale;

    public String nullable;

    public String dataDefault;

    public String comments;

    /** 一个表中所以varchar类型字段总长度 * 4；判断是否超过65535；因为MySQL中最大行大小为 65535，如果长度超过，则需要把一些字段改为text类型*/
    public Integer varcharRowSize;

    public String toMysqlScript(List<String> toTextColumns){
        StringBuilder sb = new StringBuilder();
        sb.append("`" + this.columnName + "` ");
        if("NUMBER".equalsIgnoreCase(this.dataType) || "NUMBERIC".equalsIgnoreCase(this.dataType)){
            Integer precision = 18;
            if(this.dataPrecision != null) {
                precision = this.dataPrecision;
            }

            if (this.dataScale == 0) {
                if(precision == 0) {
                    // 如果Number没指定长度
                    // 如果字段名以ID结尾，则使用bigint
                    if (this.columnName.endsWith("ID")) {
                        sb.append("bigint ");
                    }
                    // 否则使用decimal默认精度，原因是：oracle中，如果number类型不指定精度，可以存储任意长度的小数位
                    else {
                        sb.append("decimal(30, 10) ");
                    }
                } else if(precision < 3 ){
                    sb.append("tinyint ");
                } else if(precision < 5 ){
                    sb.append("smallint ");
                } else if(precision < 8){
                    sb.append("mediumint ");
                } else if(precision < 10) {
                    sb.append("int ");
                } else {
                    sb.append("bigint ");
                }
            } else {
                sb.append("decimal" + "(" + precision + "," +  this.dataScale +  ") ");
            }
        } else if (this.dataType.toUpperCase().startsWith("TIMESTAMP")) {
            sb.append("timestamp" + " ");
        } else if (this.dataType.equalsIgnoreCase("DATE")) {
            sb.append("datetime" + " ");
        } else if (this.dataType.equalsIgnoreCase("DATETIME")) {
            sb.append("datetime" + " ");
        } else if (this.dataType.equalsIgnoreCase("CLOB")) {
            sb.append("longtext" + " ");
        } else if (this.dataType.contains("BLOB")) {
            sb.append("longblob" + " ");
        }
        else if (this.dataType.startsWith("VARCHAR") || this.dataType.startsWith("NVARCHAR2")) {
            Integer length = 64;
            if(this.dataLength != null) {
                length = this.dataLength;
            }

            // SQL 错误 [1118] [42000]: Row size too large. The maximum row size for the used table type
            //    原因：
            //    从字面的意思理解就是【所用表类型（不包括 BLOBs）的最大行大小为 65535，您必须将某些列更改为TEXT或 BLOBs】；
            //    1.如果数据库编码为utf8mb4，表所有varchar字段总长度(Length)：Length*4不能大于65535；
            //    2.如果数据库编码为utf8，表所有varchar字段总长度(Length)：Length*3不能大于65535；
            //
            //    解决方案：
            //    将建表SQL中varchar长度比较大(比如超过2000)的字段，类型修改为TEXT，避免varchar总长度超过65535即可；
            if (toTextColumns.contains(this.columnName)) {
                sb.append("text" + " ");
            }
            else if (this.dataLength > 2000) {
                sb.append("text" + " ");
            }
            else {
                sb.append("varchar" + "(" + length + ") ");
            }

        } else if (this.dataType.contains("FLOAT")) {
            sb.append("float" + " ");
        } else if (this.dataType.contains("DOUBLE")) {
            sb.append("double" + " ");
        }
        else {
            sb.append(this.dataType + "(" + this.dataLength + ") ");
        }
        if ("N".equalsIgnoreCase(this.nullable)){
            sb.append("NOT NULL ");
        }
        if (StrUtil.isNotEmpty(this.dataDefault)) {
            if (this.dataType.equalsIgnoreCase("DATE") || this.dataType.equalsIgnoreCase("DATETIME")
            || this.dataType.toUpperCase().startsWith("TIMESTAMP")) {
                if (this.dataDefault.toUpperCase().contains("SYSDATE") || this.dataDefault.toUpperCase().contains("CURRENT_TIMESTAMP")) {
                    sb.append("DEFAULT NOW() ");
                } else {
                    sb.append("DEFAULT '" + this.dataDefault + "' ");
               }
            }
            else if ("NUMBER".equalsIgnoreCase(this.dataType) || "NUMBERIC".equalsIgnoreCase(this.dataType)) {
                if (this.dataDefault.toUpperCase().contains(".NEXTVAL")){
                    // 主键，不需要默认值
                } else if (NumberUtil.isNumber(this.dataDefault)) {
                    sb.append("DEFAULT " + this.dataDefault + " ");
                }
                else {
                    // 数字默认值包含括号(0)
                    if (this.dataDefault.contains("(")) {
                        String value = StrUtil.subBetween(this.dataDefault, "(",")");
                        if (NumberUtil.isNumber(value)) {
                            sb.append("DEFAULT " + value + " ");
                        }
                    }
                    // 数字默认值包含单引号'0'
                    else if (this.dataDefault.contains("'")) {
                        String value = StrUtil.subBetween(this.dataDefault, "'","'");
                        if (NumberUtil.isNumber(value)) {
                            sb.append("DEFAULT " + value + " ");
                        }
                    } else {
                        sb.append("DEFAULT NULL ");
                    }
                }
            }
            else if ("CHAR".equalsIgnoreCase(this.dataType)) {
                sb.append("DEFAULT " + this.dataDefault + " ");
            }
            else if ("VARCHAR2".equalsIgnoreCase(this.dataType) || "NVARCHAR2".equalsIgnoreCase(this.dataType)) {
                // 默认值包含符号的，都是函数，默认值不能使用，所以不做处理
                if(this.dataDefault.contains("_")
                        || this.dataDefault.contains("(")
                        || this.dataDefault.contains(")")) {
                    // 不做处理
                } else {
                    sb.append("DEFAULT " + this.dataDefault + " ");
                }
            }
            else {
                sb.append("DEFAULT '" + dataDefault + "' ");
            }
        } else if ("Y".equalsIgnoreCase(this.nullable)){
            if ( this.dataType.toUpperCase().startsWith("TIMESTAMP")) {
                // MySQL中，时间戳必须有默认值
                sb.append("DEFAULT NOW() ");
            } else {
                sb.append("DEFAULT NULL ");
            }
        }
        if (StrUtil.isNotEmpty(this.comments)){
            sb.append("COMMENT '" + this.comments + "'");
        }

        return sb.toString();
    }


    public String getColumnName() {
        return columnName;
    }

    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }

    public String getDataType() {
        return dataType;
    }

    public void setDataType(String dataType) {
        this.dataType = dataType;
    }

    public Integer getDataLength() {
        return dataLength;
    }

    public void setDataLength(Integer dataLength) {
        this.dataLength = dataLength;
    }

    public Integer getDataPrecision() {
        return dataPrecision;
    }

    public void setDataPrecision(Integer dataPrecision) {
        this.dataPrecision = dataPrecision;
    }

    public Integer getDataScale() {
        return dataScale;
    }

    public void setDataScale(Integer dataScale) {
        this.dataScale = dataScale;
    }

    public String getNullable() {
        return nullable;
    }

    public void setNullable(String nullable) {
        this.nullable = nullable;
    }

    public String getDataDefault() {
        return dataDefault;
    }

    public void setDataDefault(String dataDefault) {
        this.dataDefault = dataDefault;
    }

    public String getComments() {
        return comments;
    }

    public void setComments(String comments) {
        this.comments = comments;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public Integer getColumnId() {
        return columnId;
    }

    public void setColumnId(Integer columnId) {
        this.columnId = columnId;
    }

    public Integer getCharLength() {
        return charLength;
    }

    public void setCharLength(Integer charLength) {
        this.charLength = charLength;
    }

    public Integer getVarcharRowSize() {
        return varcharRowSize;
    }

    public void setVarcharRowSize(Integer varcharRowSize) {
        this.varcharRowSize = varcharRowSize;
    }
}
